/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package envmailsicep;

import com.entersoft.sicep.log4j.Log4jUtil;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.Calendar;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 *
 * @author acampos
 */
public class ObtMonedas {
    private static org.apache.log4j.Category logger = Log4jUtil.instance().getCategory(ObtMonedas.class);
    private String dolarUrl = "http://www.bcentral.cl/estadisticas-economicas/series-indicadores/xls/Dolar_observado.xls";
    private String destinationFile = "C:\\dataSicep\\dolar.xsl";
    private String ufUrl = "http://www.bcentral.cl/estadisticas-economicas/series-indicadores/xls/UF.xls";
    private String destinationUfFile = "C:\\dataSicep\\uf.xsl";
    private Double uf;
    private Double dolar;
    
    public ObtMonedas(){
    }
    
    public boolean descargaDolar(){
        try{
            URL url;
            url = new URL(dolarUrl);
            InputStream is;
            is = url.openStream();
            OutputStream os;
            os = new FileOutputStream(destinationFile);

            byte[] b = new byte[2048];
            int length;
            while ((length = is.read(b)) != -1) {
                os.write(b, 0, length);
            }
            is.close();
            os.close();
            return true;
        } catch (MalformedURLException ex) {
            logger.error("Dolar - MalformedURLException: " + ex.getMessage());
           //ex.printStackTrace();
        }catch (FileNotFoundException ex) {
            //ex.printStackTrace();
            logger.error("Dolar - FileNotFoundException: " + ex.getMessage());
        }catch (IOException ex) {
            //ex.printStackTrace();
            logger.error("Dolar - IOException: " + ex.getMessage());
        }
        return false;
    }
    
    public boolean leeDolar(){
        try {
            File inputWorkbook = new File(destinationFile);
            Workbook w;
            WorkbookSettings opciones= new WorkbookSettings();
            opciones.setEncoding("iso-8859-1");
            w = Workbook.getWorkbook(inputWorkbook, opciones);

            //VALIDACION PASIVO
            Sheet sheet = w.getSheet(0);
            // validar campos de pasivo
            Calendar cal = Calendar.getInstance();
            int mes = (cal.get(Calendar.MONTH)+2);
            int dia = (cal.get(Calendar.DATE)+7);
            dolar = (Double) Double.parseDouble(sheet.getCell(mes, dia).getContents().replaceAll(",", "."));
            return true;
        } catch (BiffException e) {
            //e.printStackTrace();
            logger.error("Dolar - BiffException: " + e.getMessage());
        } catch (IOException ex) {
            //ex.printStackTrace();
            logger.error("Dolar - IOException: " + ex.getMessage());
        }
        return false;
    }
    
    public boolean descargaUf(){
        try{
            URL url;
            url = new URL(ufUrl);
            InputStream is;
            is = url.openStream();
            OutputStream os;
            os = new FileOutputStream(destinationUfFile);

            byte[] b = new byte[2048];
            int length;
            while ((length = is.read(b)) != -1) {
                os.write(b, 0, length);
            }
            is.close();
            os.close();
            return true;
        } catch (MalformedURLException ex) {
            logger.error("UF - MalformedURLException: " + ex.getMessage());
           //ex.printStackTrace();
        }catch (FileNotFoundException ex) {
            //ex.printStackTrace();
            logger.error("UF - FileNotFoundException: " + ex.getMessage());
        }catch (IOException ex) {
            //ex.printStackTrace();
            logger.error("UF - IOException: " + ex.getMessage());
        }
        return false;
    }
    
    public boolean leeUf(){
        try {
            File inputWorkbook = new File(destinationUfFile);
            Workbook w;
            WorkbookSettings opciones= new WorkbookSettings();
            opciones.setEncoding("iso-8859-1");
            w = Workbook.getWorkbook(inputWorkbook, opciones);

            //VALIDACION PASIVO
            Sheet sheet = w.getSheet(1);
            // validar campos de pasivo
            Calendar cal = Calendar.getInstance();
            int mes = (cal.get(Calendar.MONTH)+2);
            int dia = ((sheet.getRows() -35) + cal.get(Calendar.DATE));
            //System.out.println(sheet.getCell(mes, dia).getContents());
            uf = (Double) Double.parseDouble((sheet.getCell(mes, dia).getContents().replace(".", "")).replaceAll(",", "."));
            return true;
        } catch (BiffException e) {
            //e.printStackTrace();
            logger.error("UF - BiffException: " + e.getMessage());
        } catch (IOException ex) {
            //ex.printStackTrace();
            logger.error("UF - IOException: " + ex.getMessage());
        }
        return false;
    }
    
    public void GuardaValores(){
            logger.info("Carga parametro en Clase :  " + this.getClass().getName() + "modulo : GuardaValores()" );
            Session session = HibernateUtil.getSessionFactory().openSession();
            Transaction tx = session.beginTransaction();
            Calendar cal = Calendar.getInstance();
            try{

                Query query = session.getNamedQuery("PRO_SCP_upd_CambioMoneda_ini");
                //query.setTimeout(Integer.parseInt(FacesContext.getCurrentInstance().getExternalContext().getInitParameter("timeOutSql")));
                //query.setInteger("usu",(Integer)FacesContext.getCurrentInstance(). getExternalContext().getSessionMap().get("idUsuario"));
                
                String fecha = "" + cal.get(Calendar.YEAR);
                if ((cal.get(Calendar.MONTH)+1) <10){
                    fecha += "0" + (cal.get(Calendar.MONTH)+1);
                }else{
                    fecha += "" + (cal.get(Calendar.MONTH)+1);
                }
                
                if (cal.get(Calendar.DATE) <10){
                    fecha += "0" + cal.get(Calendar.DATE);
                }else{
                    fecha += "" + cal.get(Calendar.DATE);
                }
                
                query.setString("fecha", fecha);
                query.setDouble("dolar", dolar);
                query.setDouble("uf", uf);

                logger.debug(" Sentencia Query["+query.getQueryString()+"]");

                List<GuardaUfDolar> lst = query.list();
                if (!lst.isEmpty()){
                    for (int i = 0; i < lst.size(); i++){
                        if(lst.get(i).getRetorno() == 0){
                            logger.info(lst.get(i).getMensaje());
                        }else{
                            logger.error(lst.get(i).getRetorno());
                            logger.error(lst.get(i).getMensaje());
                        }
                    }
                }
            }catch (HibernateException sqle) {
                logger.error("HibernateException: " + sqle.getMessage());
                if(sqle.getCause() != null)
                    logger.error("SQLException: " + sqle.getCause().getMessage());
                tx.rollback();
            }catch (Exception j){
                logger.error("[" + this.getClass().getName() + ".init] finally  - Error Exception. "+ j);
            }finally {
                if (session != null) {
                    if (session.isConnected()) {
                        tx.commit();
                        session.close();
                    }
                }
            }        
    }
    
}
